package org.cp.dao;

import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.LinkedList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.BeanProcessor;

public class DaoHelperImpl implements DaoHelper {
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(DaoHelperImpl.class);

	// private ConnectionPool pool;

	// spring set
	private DataSource dataSource;

	private static final BeanProcessor beanProcesser = new BeanProcessor();

	// public void setPool(ConnectionPool pool) {
	// this.pool = pool;
	// }

	public boolean add(String sql, Connection conn, Object... args) {
		return this.update(sql, conn, args) == 1;
	}

	public boolean add(String sql, Object... args) {

		return this.update(sql, args) == 1;

	}

	public boolean delete(String sql, Connection conn, Object... args) {

		return this.update(sql, conn, args) == 1;

	}

	public boolean delete(String sql, Object... args) {

		return this.update(sql, args) == 1;

	}

	public List<Object> getList(String sql, Class obj, Object... args) {

		logger.debug(sql);
		List<Object> list = new LinkedList<Object>();
		ResultSet rs = null;
		PreparedStatement ps = null;
		Connection conn = null;
		int i = 1;
		try {
			conn = this.getConn();
			ps = conn.prepareStatement(sql);
			for (Object arg : args) {
				ps.setObject(i, arg);
				i++;
			}

			rs = ps.executeQuery();

			while (rs.next()) {
				list.add(beanProcesser.toBean(rs, obj));
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();

			return null;
		} catch (NullPointerException e) {
			e.printStackTrace();
			return null;
		} finally {
			this.returnConnection(conn);
			rs = null;
			ps = null;
		}
		return list;

	}

	public Object getObject(String sql, Connection conn, Class obj,
			Object... args) {

		logger.debug(sql);
		Object c = null;
		ResultSet rs = null;
		PreparedStatement ps = null;

		int i = 1;
		try {

			ps = conn.prepareStatement(sql);
			for (Object arg : args) {
				ps.setObject(i, arg);
				i++;
			}

			rs = ps.executeQuery();

			while (rs.next()) {
				c = beanProcesser.toBean(rs, obj);
			}
			ps.close();
			rs.close();
			return c;
		} catch (SQLException e) {

			e.printStackTrace();

			return null;
		} catch (NullPointerException e) {
			e.printStackTrace();
			return null;
		} finally {

			rs = null;
			ps = null;
		}
	}

	public Object getObject(String sql, Class obj, Object... args) {
		logger.debug(sql);
		Object c = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		Connection conn = null;
		int i = 1;
		try {
			conn = this.getConn();
			ps = conn.prepareStatement(sql);
			for (Object arg : args) {
				ps.setObject(i, arg);
				i++;
			}

			rs = ps.executeQuery();
			while (rs.next()) {
				c = beanProcesser.toBean(rs, obj);
			}
			ps.close();
			rs.close();

		} catch (SQLException e) {

			e.printStackTrace();

		} catch (NullPointerException e) {
			e.printStackTrace();

		} finally {
			this.returnConnection(conn);
			rs = null;
			ps = null;
		}
		return c;
	}

	public Boolean queryForBoolean(String sql, Object... args) {

		return this.queryForLong(sql, args) == 1;

	}

	public Long queryForLong(String sql, Object... args) {
		logger.debug(sql);
		ResultSet rs = null;
		PreparedStatement ps = null;
		Connection conn = null;
		int i = 1;
		long result = 0;
		String res = null;
		try {
			conn = this.getConn();
			ps = conn.prepareStatement(sql);
			for (Object arg : args) {
				ps.setObject(i, arg);
				i++;
			}
			rs = ps.executeQuery();

			while (rs.next()) {
				res = rs.getString(1);
				if (res != null)
					result = Long.valueOf(rs.getString(1));
			}

			rs.close();
			ps.close();

		} catch (SQLException e) {
			e.printStackTrace();

		} catch (NullPointerException e) {
			e.printStackTrace();

		} finally {
			this.returnConnection(conn);
			rs = null;
			ps = null;
		}
		return result;
	}

	public String queryForString(String sql, Object... args) {
		logger.debug(sql);
		ResultSet rs = null;
		PreparedStatement ps = null;
		Connection conn = null;
		int i = 1;
		try {
			conn = this.getConn();
			ps = conn.prepareStatement(sql);
			for (Object arg : args) {
				ps.setObject(i, arg);
				i++;
			}
			rs = ps.executeQuery();

			String result = null;
			while (rs.next()) {
				result = rs.getString(1);
			}

			rs.close();
			ps.close();
			return result;

		} catch (SQLException e) {
			e.printStackTrace();
			this.rollBack(conn);

		} catch (NullPointerException e) {
			e.printStackTrace();

		} finally {
			this.returnConnection(conn);
			rs = null;
			ps = null;
		}
		return null;
	}

	protected void fillStatement(PreparedStatement stmt, Object[] params)
			throws SQLException {

		if (params == null) {
			return;
		}

		for (int i = 0; i < params.length; i++) {
			if (params[i] != null) {
				stmt.setObject(i + 1, params[i]);
			} else {
				// VARCHAR works with many drivers regardless
				// of the actual column type. Oddly, NULL and
				// OTHER don't work with Oracle's drivers.
				stmt.setNull(i + 1, Types.VARCHAR);
			}
		}

	}

	public int update(String sql, Connection conn, Object... params) {
		logger.debug(sql);
		PreparedStatement stmt = null;
		int rows = 0;

		try {
			conn.setAutoCommit(false);
			stmt = conn.prepareStatement(sql);
			this.fillStatement(stmt, params);
			rows = stmt.executeUpdate();
			stmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (NullPointerException e) {
			e.printStackTrace();

		} finally {
			stmt = null;
		}

		return rows;
	}

	public int update(String sql, Object... params) {
		logger.debug(sql);
		PreparedStatement stmt = null;
		Connection conn = null;
		int rows = 0;
		try {
			conn = this.getConn();
			conn.setAutoCommit(false);
			stmt = conn.prepareStatement(sql);
			this.fillStatement(stmt, params);
			rows = stmt.executeUpdate();
			conn.commit();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
			this.rollBack(conn);
		} catch (NullPointerException e) {
			e.printStackTrace();
		} finally {
			this.returnConnection(conn);
		}

		return rows;
	}

	public Connection getConn() {
		logger.debug("get connetion...");
		Connection conn = null;
		try {
			conn = dataSource.getConnection();

		} catch (SQLException e) {

			e.printStackTrace();
		}
		return conn;
	}

	public void returnConnection(Connection conn) {
		logger.debug("return connetion...");
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public void rollBack(Connection conn) {
		logger.debug("rollback...");
		try {
			conn.rollback();
		} catch (SQLException e) {

			e.printStackTrace();
		}

	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

}
